﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.Entity;
using System.Web.Mvc;
using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.SYS;

namespace KPIS.GERP.WCF.GIMS
{
    [ServiceContract]
    public interface IDispenseService
    {
        #region SelectListItem

        [OperationContract]
        List<SelectListItem> GetRequest();

        #endregion

        #region RequestParcel

        [OperationContract]
        IEnumerable<RequestParcel> SelectRequestParcel(RequestParcel criteriaModel = null, List<string> includeSubObjectString = null);

        #endregion

        #region GetString, int

        [OperationContract]
        int GetSumQuantity(string strType, int intResItemId, int intProjId, int intWhId);

        [OperationContract]
        int GetProjExp(int intProjId);

        [OperationContract]
        string GetDispenseQty(int intDispId, int intResItemId, string strParcelType);


        #endregion

        #region Dispense

        [OperationContract]
        IEnumerable<Dispense> SelectDispense(Dispense criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int Insert(Dispense model, int createByUserId);

        [OperationContract]
        void Update(Dispense model, int updateByUserId);

        [OperationContract]
        void Delete(int id, int deleteByUserId);

        #endregion

        #region DispenseHistory

        [OperationContract]
        int InsertDispenseHistory(DispenseHistory model, int createByUserId);

        #endregion

        #region MovementHistory

        [OperationContract]
        int InsertMovementHistory(MovementHistory model, int createByUserId);

        #endregion

        #region OfficeSupplies

        [OperationContract]
        IEnumerable<OfficeSuppliesLot> SelectOfficeSuppliesLot(OfficeSuppliesLot criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        void UpdateOfficeSupplies(OfficeSuppliesLot model, int updateByUserId);

        #endregion

        #region Asset

        [OperationContract]
        IEnumerable<Asset> SelectAsset(Asset criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        void UpdateAsset(Asset model, int updateByUserId);

        #endregion

        #region RequestParcelDtl

        [OperationContract]
        IEnumerable<RequestParcelDtl> SelectRequestParcelDtl(RequestParcelDtl criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        void UpdateRequestParcelDtl(RequestParcelDtl model, int updateByUserId);

        #endregion

        #region Report

        [OperationContract]
        string[] DispenstReportHead(int DispenseId);

        [OperationContract]
        List<Dictionary<string, string>> DispensReportDtls(int DispenseId);

        #endregion
    }
    
    public class DispenseService : IDispenseService
    {

        #region SelectListItem

        public List<SelectListItem> GetRequest()
        {
            var data = new List<SelectListItem>();
            var context = new GERPContext(false);
            try
            {
                var query1 = (from m in context.RequestParcels
                              where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              && m.RecordStatus == Models.SYS.RecordStatus.ApprovedStatus
                              select m).ToList();

                var query2 = (from m in context.Dispenses
                              join n in context.RequestParcels on m.RequestParcelId equals n.ReqParcelId
                              where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              select n).ToList();

                var subquery = query1.Except(query2).ToList();

                foreach (var Request in subquery)
                {
                    var item = new SelectListItem { Text = Request.IcmReqCode, Value = Request.ReqParcelId.ToString() };
                    data.Add(item);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        #endregion

        #region RequestParcel

        public IEnumerable<RequestParcel> SelectRequestParcel(RequestParcel criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<RequestParcel> query = context.RequestParcels;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region GetString, int


        public int GetSumQuantity(string strType, int intResItemId, int intProjId, int intWhId)
        {
            var context = new GERPContext();
            int intSumQty = 0;
            try
            {
                int intProjExpId = (from f in context.ProjectExpenses
                                    join g in context.Projects on f.ProjId equals g.ProjId
                                    where g.ProjId == intProjId
                                    select g.ProjId).FirstOrDefault();

                if (strType == "OS")
                {
                    var ListOffice = (from a in context.OfficeSuppliesLots
                                      where a.ResItemId == intResItemId
                                      && a.WhId == intWhId
                                      && a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                      select a).OrderBy(x => x.CreatedWhen).ToList();

                    var list = ListOffice
                        //.Where(x => x.ProjExpId == intProjExpId || x.ProjExpId == null)
                        .ToList();

                    intSumQty = list.Sum(x => x.Quantity);
                }
                else {
                    var ListAsset = (from a in context.Assets
                                      where a.ResItemId == intResItemId
                                      && a.WhId == intWhId
                                      && a.JobSiteId == null
                                      && a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                      select a).OrderBy(x => x.CreatedWhen).ToList();

                    var list = ListAsset
                        //.Where(x => x.ProjExpId == intProjExpId || x.ProjExpId == null)
                        .ToList();

                    intSumQty = list.Sum(x => x.Quantity);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intSumQty;
        }


        public int GetProjExp( int intProjId)
        {
            var context = new GERPContext();
            int intProjExpId = 0;
            try
            {
                intProjExpId = (from f in context.ProjectExpenses
                                    join g in context.Projects on f.ProjId equals g.ProjId
                                    where g.ProjId == intProjId
                                    select g.ProjId).FirstOrDefault();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intProjExpId;
        }


        public string GetDispenseQty(int intDispId, int intResItemId, string strParcelType)
        {
            string strQty = "0";
            var context = new GERPContext(false);
            try
            {
                if (strParcelType == "OS")
                {
                    var queryOffice = (from m in context.DispenseHistorys
                                       where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                       && m.DispenseId == intDispId && m.ResItemId == intResItemId
                                       select m).ToList().Count();
                    if (queryOffice > 0)
                    {
                        var DispQty = (from m in context.DispenseHistorys
                                       where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                       && m.DispenseId == intDispId && m.ResItemId == intResItemId
                                       select m).ToList();
                        strQty = DispQty.Sum(x=>x.Quantity).ToString();
                    }
                }
                else
                {
                    //string strDispId = intDispId.ToString();
                    var queryAsset = (from m in context.MovementHistorys
                                      join n in context.Assets on m.AssetId equals n.AssetId
                                      where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                      && m.MoveRefNo == intDispId && n.ResItemId == intResItemId
                                      select m).ToList().Count();
                    if (queryAsset > 0)
                    {
                        var assetQty = (from m in context.MovementHistorys
                                        join n in context.Assets on m.AssetId equals n.AssetId
                                        where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                        && m.MoveRefNo == intDispId && n.ResItemId == intResItemId
                                        && n.JobSiteId != null
                                        select n).ToList().Count();
                        strQty = assetQty.ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return strQty;
        }


        #endregion

        #region Dispense


        public IEnumerable<Dispense> SelectDispense(Dispense criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Dispense> query = context.Dispenses;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public int Insert(Dispense model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    //model.Code = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.Dispense);
                    context.Dispenses.Add(model);
                    context.SaveChanges();

                    return model.DispenseId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(Dispense model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var model = context.Dispenses.Find(id);
                    if (model == null) return;

                    context.Entry(model).State = EntityState.Modified;
                    model.RecordStatus = RecordStatus.DeletedStatus;
                    model.UpdatedBy = deleteByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #endregion

        #region DispenseHistory

        public int InsertDispenseHistory(DispenseHistory model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    context.DispenseHistorys.Add(model);
                    context.SaveChanges();

                    return model.DispenseId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region MovementHistory

        public int InsertMovementHistory(MovementHistory model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedBy = createByUserId;
                    model.CreatedWhen = DateTime.Now;
                    context.MovementHistorys.Add(model);
                    context.SaveChanges();

                    return model.MoveHistId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region OfficeSupplies

        public IEnumerable<OfficeSuppliesLot> SelectOfficeSuppliesLot(OfficeSuppliesLot criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<OfficeSuppliesLot> query = context.OfficeSuppliesLots;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateOfficeSupplies(OfficeSuppliesLot model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Asset

        public IEnumerable<Asset> SelectAsset(Asset criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Asset> query = context.Assets;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateAsset(Asset model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region RequestParcelDtl

        public IEnumerable<RequestParcelDtl> SelectRequestParcelDtl(RequestParcelDtl criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<RequestParcelDtl> query = context.RequestParcelDtls;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateRequestParcelDtl(RequestParcelDtl model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Report

        public string[] DispenstReportHead(int DispenseId)
        {
            string EmpName = "";
            string PosName = "";
            string JobSiteName = "";
            string SubJobSiteName = "";

            using (var context = new GERPContext(false))
            {
                var x = (from a in context.Dispenses
                         join b in context.Employees on a.CreatedBy equals b.EmpId into bs
                         from b in bs.DefaultIfEmpty()
                         join c in context.Positions on b.PosId equals c.PosId into cs
                         from c in cs.DefaultIfEmpty()
                         join d in context.JobSites on b.WorkingJobSiteId equals d.JobSiteId into ds
                         from d in ds.DefaultIfEmpty()
                         join e in context.JobSites on d.UpperJobSiteId equals e.JobSiteId into es
                         from e in es.DefaultIfEmpty()
                         join h in context.JobSites on e.UpperJobSiteId equals h.JobSiteId into hs
                         from h in hs.DefaultIfEmpty()
                         where a.DispenseId == DispenseId
                         select new
                         {
                             EmpName = (b.FirstName + "  " + b.LastName),
                             PosName = c.Name,
                             JobSiteName = d.Name,
                             UpperJobSiteName = e.Name,
                             d.JobSiteLevelId,
                             UpperJobSiteName2 = h.Name,
                         }).FirstOrDefault();
                if (x != null)
                {
                    EmpName = x.EmpName;
                    PosName = x.PosName;
                    if (x.JobSiteLevelId == 3 || x.JobSiteLevelId == 2)
                    {
                        JobSiteName = x.JobSiteName;
                        SubJobSiteName = "";
                    }
                    else if (x.JobSiteLevelId == 4)
                    {
                        SubJobSiteName = x.JobSiteName;
                        JobSiteName = x.UpperJobSiteName;
                    }
                    else if (x.JobSiteLevelId == 5)
                    {
                        SubJobSiteName = x.UpperJobSiteName;
                        JobSiteName = x.UpperJobSiteName2;
                    }
                }
            }
            return new string[] { EmpName, PosName, JobSiteName, SubJobSiteName };
        }

        public List<Dictionary<string, string>> DispensReportDtls(int DispenseId)
        {
            using (var context = new GERPContext(false))
            {
                var x = (from a in context.Dispenses
                         join b in context.MovementHistorys on a.DispenseId equals b.MoveRefNo
                         join c in context.Assets on b.AssetId equals c.AssetId
                         where a.DispenseId == DispenseId
                         select new { c.AssetName, c.AssetCode}).ToList();

                var y = (from a in context.Dispenses
                         join b in context.DispenseHistorys on a.DispenseId equals b.DispenseId
                         join c in context.MasResourceItems on b.ResItemId equals c.ResItemId
                         where a.DispenseId == DispenseId
                         select new { c.Name, b.LotNo }).ToList();

                List<Dictionary<string, string>> Dics = new List<Dictionary<string, string>>();
                int nIndex = 1;
                foreach (var item in x)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();

                    dic.Add("No", nIndex.ToString());
                    dic.Add("AssetName", item.AssetName);
                    dic.Add("AssetCode", item.AssetCode);
       
                    Dics.Add(dic);
                    nIndex++;
                }

                foreach (var item in y)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();

                    dic.Add("No", nIndex.ToString());
                    dic.Add("AssetName", item.Name);
                    dic.Add("AssetCode", item.LotNo);

                    Dics.Add(dic);
                    nIndex++;
                }

                return Dics;
            }

        }

        #endregion
    }
}
